﻿using System.Collections.Generic;
using ExamPlat.ViewModels;
using ExamPlat.Models;
using ExamPlat.DAL;
using System.Data;
using System;

namespace ExamPlat.Repositories
{
    public class UserInfoRepository : IUserInfoRepository
    {
        /// <summary>
        /// 增加用户
        /// </summary>
        /// <param name="model">用户信息</param>
        /// <returns></returns>
        public int AddUserInfo(UserInfo model)
        {
            try
            {
                string sql = string.Format("select count(1) from UserInfo where LoginAccount = '{0}'", model.LoginAccount);
                int result = SqlHelper.ExecuteScalar(sql);
                if (result == 0)
                {
                    sql = string.Format("insert into UserInfo(LoginAccount,LoginPwd,UserName,RoleID) values('{0}','{1}','{2}',{3})", model.LoginAccount, model.LoginPwd, model.UserName, model.RoleID);
                    return SqlHelper.ExecuteNonQuery(sql);
                }
                return -1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 修改用户
        /// </summary>
        /// <param name="model">用户信息</param>
        /// <returns></returns>
        public int UpdateUserInfo(UserInfo model)
        {
            try
            {
                string sql = string.Format("select count(1) from UserInfo where LoginAccount = '{0}' and ID != {1}", model.LoginAccount, model.ID);
                int result = SqlHelper.ExecuteScalar(sql);
                if (result == 0)
                {
                    sql = string.Format("update UserInfo set LoginAccount='{0}',LoginPwd='{1}',UserName='{2}',RoleID={3} where ID = {4}", model.LoginAccount, model.LoginPwd, model.UserName, model.RoleID, model.ID);
                    return SqlHelper.ExecuteNonQuery(sql);
                }
                return -1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="id">用户ID</param>
        /// <returns></returns>
        public int DelUserInfo(int id)
        {
            try
            {
                string sql = string.Format("delete from UserInfo where ID = {0} ", id);
                return SqlHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据用户ID查询用户信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public UserInfo GetUserInfoByID(int id)
        {
            try
            {
                string sql = string.Format("select * from UserInfo  where ID = {0} ", id);
                DataSet ds = SqlHelper.ExecuteQuery(sql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    UserInfo result = new UserInfo();
                    DataTable dt = ds.Tables[0];
                    result.ID = (int)dt.Rows[0]["ID"];
                    result.LoginAccount = dt.Rows[0]["LoginAccount"].ToString();
                    result.LoginPwd = dt.Rows[0]["LoginPwd"].ToString();
                    result.UserName = dt.Rows[0]["UserName"].ToString();
                    result.RoleID = (int)dt.Rows[0]["RoleID"];
                    return result;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据登录帐号和登录密码查询用户信息
        /// </summary>
        /// <param name="loginAccount"></param>
        /// <param name="loginPwd"></param>
        /// <returns></returns>
       public UserInfo GetUserInfoByAccountAndPwd(string loginAccount, string loginPwd)
        {
            try
            {
                string sql = string.Format("select * from UserInfo where LoginAccount='{0}' and LoginPwd='{1}'", loginAccount, loginPwd);
                DataSet ds = SqlHelper.ExecuteQuery(sql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    UserInfo result = new UserInfo();
                    DataTable dt = ds.Tables[0];
                    result.ID = (int)dt.Rows[0]["ID"];
                    result.LoginAccount = dt.Rows[0]["LoginAccount"].ToString();
                    result.LoginPwd = dt.Rows[0]["LoginPwd"].ToString();
                    result.UserName = dt.Rows[0]["UserName"].ToString();
                    result.RoleID = (int)dt.Rows[0]["RoleID"];
                    return result;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据条件查询用户信息
        /// </summary>
        /// <param name="loginAccount">登录帐号</param>
        /// <param name="userName">用户姓名</param>
        /// <returns></returns>
        public IList<ViewUserInfo> GetUserInfos(string loginAccount="", string userName="")
        {
            try
            {
                string sql = "select A.ID,A.LoginAccount,A.LoginPwd,A.UserName,B.RoleName,A.RoleID from UserInfo A left outer join RoleInfo B on A.RoleID = B.ID where 1=1";
                if (!string.IsNullOrWhiteSpace(loginAccount))
                {
                    sql += string.Format(" and LoginAccount  like '%{0}%'", loginAccount);
                }
                if (!string.IsNullOrWhiteSpace(userName))
                {
                    sql += string.Format(" and UserName  like '%{0}%'", userName);
                }
                DataSet ds = SqlHelper.ExecuteQuery(sql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    IList<ViewUserInfo> result = new List<ViewUserInfo>();
                    DataTable dt = ds.Tables[0];
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        ViewUserInfo tmpResult = new ViewUserInfo();
                        tmpResult.ID = (int)dt.Rows[i]["ID"];
                        tmpResult.LoginAccount = dt.Rows[i]["LoginAccount"].ToString();
                        tmpResult.LoginPwd = dt.Rows[i]["LoginPwd"].ToString();
                        tmpResult.UserName = dt.Rows[i]["UserName"].ToString();
                        tmpResult.UserRole = dt.Rows[i]["RoleName"].ToString();
                        tmpResult.RoleID = (int)dt.Rows[i]["RoleID"];
                        result.Add(tmpResult);
                    }
                    return result;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}